/*
DO FILE FOR REPLICATION FOR 
Not a Border Crisis, but a Labor Market Crisis: The Often Overlooked "Pull" Factor of US Border Crossings

BY DANY BAHAR, DECEMBER 2024

EDITED IN APRIL 2025 TO FIX A SMALL CODING ERROR
*/

clear all
set more off

global path "~/Brown Dropbox/Dany Bahar/BAHAR-LaborMarketTightness/submissions/JPAM/~replication_package/"

global estout_pre prehead("\begin{threeparttable}[b] \begin{tabular}{l*{@M}{c}}" "\hline\hline"  "\multicolumn{@span}{l}{\depvarnote} \\" "\hline") 
global estout_pre_panelA prehead("\begin{threeparttable}[b] \begin{tabular}{l*{@M}{c}}" "\hline\hline" "\multicolumn{@span}{l}{\depvarnoteA} \\ \hline")
global estout_pre_panelB prehead("\hline" "\multicolumn{@span}{l}{\depvarnoteB} \\ \hline")
global estout_pre_panelC prehead("\hline" "\multicolumn{@span}{l}{\depvarnoteC} \\ \hline")
global estout_post postfoot("\hline"  "\end{tabular}" "\begin{tablenotes} \item {\footnotesize \tablenote}" "\item {\footnotesize \$^{*}p<0.10, ^{**}p<0.05, ^{***}p<0.01\$}" "\end{tablenotes}" "\end{threeparttable}")

global estout_params cells(b(fmt(3)) se(star par fmt(3))) starlevels(* .1 ** .05 *** .01) varlabels(_cons Constant) label stats(N r2, labels("Observations" "$ R^2 $") fmt(%6.0fc 2))  style(tex) collabels(none) posthead(\hline)   
global estout_params_txt cells(b(fmt(3)) se(star par fmt(3))) starlevels(* .1 ** .05 *** .01) varlabels(_cons Constant) label stats(N r2, labels("Observations" "$ R^2 $") fmt(%6.0fc 2)) 
global estout_params_logit cells(b(fmt(3)) se(star par fmt(3))) starlevels(* .1 ** .05 *** .01) varlabels(_cons Constant) label stats(N r2_p, fmt(%6.0fc 2) labels(N r2\_p)) style(tex) collabels(none) posthead(\hline) 
global estout_params_txt_logit cells(b(fmt(3)) se(star par fmt(3))) starlevels(* .1 ** .05 *** .01) varlabels(_cons Constant) label stats(N r2_p, fmt(%6.0fc 2)) 
global estout_params_IV cells(b(fmt(3)) se(star par fmt(3))) starlevels(* .1 ** .05 *** .01) varlabels(_cons Constant) label stats(N widstat, fmt(%6.0fc 2) labels("Observations" "KP F-Stat"))  style(tex) collabels(none) posthead(\hline) 
global estout_params_IV_txt cells(b(fmt(3)) se(star par fmt(3))) starlevels(* .1 ** .05 *** .01) varlabels(_cons Constant) label stats(N widstat, fmt(%6.0fc 2) labels("Observations" "KP F-Stat")) 

global estout_params_nostats cells(b(fmt(3)) se(star par fmt(3))) starlevels(* .1 ** .05 *** .01) varlabels(_cons Constant) label  style(tex) collabels(none) posthead(\hline)   
global estout_params_nostats_txt cells(b(fmt(3)) se(star par fmt(3))) starlevels(* .1 ** .05 *** .01) varlabels(_cons Constant) label
global estout_stats_cpfe stats(N r2 cpfe, labels("Observations" "R$^2$" "cpFE") fmt(%6.0fc 2))
global estout_stats_IV_cpfe stats(N widstat cpfe, labels("Observations" "KP F-Stat" "cpFE") fmt(%6.0fc 2))


/*** SETTING UP THE DATA ***/
   
//import delimited "${path}/rawdata/OpeningsLevels_NotSeasonallyAdj.csv", delimiter(",") clear
import delimited "${path}data/OpeningsLevels_NotSeasonallyAdj_2024.csv", delimiter(",") clear
ren jtujol openings_nsa
ren date date_str
replace date_str = subinstr(date_str,"-","/",.)
replace date_str = substr(date_str,1,7)
gen date=monthly(date_str, "YM",2000)
format %tm date
drop date_str
label var openings_nsa "Total openings level (in thousands), not seasonally adjusted"
tempfile openings
save `openings'

//import delimited "${path}/rawdata/UnemploymentLevels_NotSeasonallyAdj.csv", delimiter(",") clear
import delimited "${path}/data/UnemploymentLevels_NotSeasonallyAdj_2024.csv", delimiter(",") clear
ren lnu03000000 unemployed_nsa
ren date date_str
replace date_str = subinstr(date_str,"-","/",.)
replace date_str = substr(date_str,1,7)
gen date=monthly(date_str, "YM")
format %tm date
drop date_str
label var unemployed_nsa "Total unemployment level (in thousands), not seasonally adjusted"
tempfile unemp
save `unemp'


//import delimited "${path}/rawdata/crossings_data_new.csv", delimiter(",") clear
import delimited "${path}/data/crossings_data_2024.csv", delimiter(",") clear
keep year month southwest_crossings total_crossings *_xings
drop if mi(year)
//drop date
g date_str = string(month)+"/"+string(year)
//g date = date(date_str, "DMY")
//format date %td
//drop date
gen date=monthly(date_str, "MY")
format %tm date
drop date_str
g totalencounters = southwest_crossings
replace totalencounters = subinstr(totalencounters,",","",.)
destring totalencounters, replace
ren totalencounters encounters_nsa

qui ds *_xings
local vlist = r(varlist)
foreach v of local vlist {
	replace `v' = subinstr(`v',",","",.)
	destring `v', replace
}
label var encounters_nsa "Total Encounters US SW border, not seasonally adjusted"

//Merge 
merge 1:1  date using `openings', keep(master matched using) nogen
merge 1:1  date using `unemp', keep(master matched) nogen
order date month year *

drop date
g date_str = string(month)+"/"+string(year)
gen date=monthly(date_str, "MY")
format %tm date

tsset date

g openingsrate_nsa = openings_nsa / unemployed_nsa
foreach v in openingsrate encounters {
	tsfilter hp `v'_cycles  = `v'_nsa, trend(`v'_sa)
	//g `v'_sa = `v'_nsa-`v'_cycles
	drop `v'_cycles
}

g log_openings_nsa = log(openingsrate_nsa)
g log_encounters_nsa = log(encounters_nsa)
g log_openings_sa = log(openingsrate_sa)
g log_encounters_sa = log(encounters_sa)


g d12_openingsrate_nsa = openingsrate_nsa - L12.openingsrate_nsa
g d12_encounters_nsa = encounters_nsa - L12.encounters_nsa
g d12_log_encounters_nsa = log_encounters_nsa-L12.log_encounters_nsa
g d12_log_openings_nsa = log_openings_nsa-L12.log_openings_nsa

/*** FIGURES APPENDIX A1 AND A2 ***/
twoway (line encounters_nsa date), scheme(s1color) ytitle("SW Border Crossings") xtitle("Months") 
graph export "${path}graphs/MonthlyCrossings.eps", as(eps) replace

twoway (line openingsrate_nsa date), scheme(s1color) ytitle("Job Openings per Unemployed") xtitle("Months") 
graph export "${path}graphs/MonthlyOpenings.eps", as(eps) replace

/*** FIGURE 1 ***/
tempfile g1 g2 g3

twoway (line openingsrate_nsa date, lp(dash)) (line encounters_nsa date, yaxis(2)), scheme (s1color) legend(rows(2)) ytitle("Job Openings per Unemployed Ratio") ytitle("Encounters (Raw)", axis(2)) xtitle(Month) legend(order(1 "Job Openings per Unemployed (monthly)" 2 "SW Border Encounters (monthly)")) name(g1, replace)
graph export "${path}graphs/PlotRawData_Monthly.eps", as(eps) replace

twoway (line openingsrate_sa date, lp(dash)) (line encounters_sa date, yaxis(2)), scheme(s1color) legend(rows(2))  ytitle("Job Openings per Unemployed Ratio") ytitle("Encounters (Raw)", axis(2))  xtitle(Month)legend(order(1 "Job Openings per Unemployed (monthly)" 2 "SW Border Encounters (monthly)")) name(g2, replace)
graph export "${path}graphs/PlotHPFilter_Monthly.eps", as(eps) replace

twoway (line d12_openingsrate_nsa date, lp(dash)) (line d12_encounters_nsa date, yaxis(2)), scheme (s1color) legend(rows(2)) ytitle("Job Openings per Unemployed Ratio") ytitle("Encounters (Raw)", axis(2)) xtitle(Month) legend(order(1 "Job Openings per Unemployed (monthly, YoY difference)" 2 "SW Border Encounters (monthly, YoY difference)")) name(g3, replace) xsize(6in)
graph export "${path}graphs/PlotYoYDiff_Monthly.eps", as(eps) replace


/*** TABLE B1 ONLINE APPENDIX ***/
//prepare controls for time series regressions (YoY)
cap drop res_levels
qui reg log_encounters_nsa log_openings_nsa
predict res_levels, res
global ts_res_controls l.res_levels l.(d12_log_encounters_nsa d12_log_openings_nsa)
//global ts_res_controls l.(d4.log_encounters_nsa d4.log_openings_nsa)


label var log_openings_nsa "Job Openings Rate (logs)"
eststo,title("Levels"): qui reghdfe log_encounters_nsa log_openings_nsa, a(month year) vce(robust)
//eststo: qui reghdfe log_encounters_nsa log_openings_nsa c.log_openings_nsa#c.(democrat), a(month year) vce(robust)
qui estadd local qfe "Y"
qui estadd local yfe "Y"
eststo, title("YoY"): qui reg d12_log_encounters_nsa d12_log_openings_nsa $ts_res_controls, robust
qui estadd local qfe "N"
qui estadd local yfe "N"
estout, $estout_params_FE_txt rename(d12_log_openings_nsa log_openings_nsa) keep(log_openings_nsa)
estout using "${path}tables/MainResults_Monthly.tex", replace $estout_params_FE $estout_pre $estout_post rename(d12_log_openings_nsa log_openings_nsa ) keep(log_openings_nsa)
estout using "${path}tables/MainResultsAllControls_Monthly.tex", replace $estout_params_FE $estout_pre $estout_post rename(d12_log_openings_nsa log_openings_nsa )
eststo clear


/*** FIGURE 2 ***/
/*Marking democratic administrations*/
g democrat = inrange(year, 1999, 2000) | inrange(year,2009,2016) | inrange(year,2021,2024)
label var democrat "Democratic Admin"

g clinton = inrange(year, 1999,2000)
label var clinton "Clinton"
g bush2 = inrange(year,2001,2008)
label var bush2 "Bush"
g obama = inrange(year,2009, 2016)
label var obama "Obama"
g trump = inrange(year,2017,2020)
label var trump "Trump"
g biden = inrange(year,2021,2024)
label var biden "Biden"

twoway (scatter log_encounters_nsa log_openings_nsa  if bush2, msymbol(t))  ///
	   (scatter log_encounters_nsa log_openings_nsa  if obama, msymbol(d)) ///
	   (scatter log_encounters_nsa log_openings_nsa  if trump, msymbol(smv))  ///
	   (scatter log_encounters_nsa log_openings_nsa  if biden, msymbol(smx))  ///
	   	(qfit log_encounters_nsa log_openings_nsa  if bush2, lp(shortdash_dot) lc(dkgreen) lw(thin)) ///
	   	(qfit log_encounters_nsa log_openings_nsa  if obama, lp(shortdash) lc(orange_red) lw(thin)) ///
	   	(qfit log_encounters_nsa log_openings_nsa  if trump, lp(dash_dot) lc(navy) lw(thin)) ///
	   	(qfit log_encounters_nsa log_openings_nsa  if biden, lp(longdash) lc(sienna) lw(thin)) ///
	   (qfit log_encounters_nsa log_openings_nsa, lw(thick) lc(black)), ///
	   legend(order(1 "Bush" 2 "Obama" 3 "Trump" 4 "Biden" 9 "Fit Pooled" 5 "Fit Bush" 6 "Fit Obama" 7 "Fit Trump" 8 "Fit Biden") ///
		rows(2) symxsize(*.5) size(small)) ///
	   ytitle("Total Encounters, monthly (logs)") xtitle("Labor Market Tightness, monthly (logs)") scheme(s1color)
graph export "${path}graphs/ScatterPresidents_Monthly_FittedLines.eps", as(eps) replace


/*** TABLE B2 ONLINE APPENDIX AND FIGURE 3 ***/
//prepare controls for time series regressions (YoY)
cap drop res_levels
qui reg log_encounters_nsa log_openings_nsa
predict res_levels, res
global ts_res_controls l.res_levels l.(d12_log_encounters_nsa d12_log_openings_nsa)
//global ts_res_controls l.(d4.log_encounters_nsa d4.log_openings_nsa)

eststo, title("Levels"): qui reghdfe log_encounters_nsa log_openings_nsa c.log_openings_nsa#c.democrat, a(month year) vce(robust)
qui estadd local qfe "Y"
qui estadd local yfe "Y"
eststo, title("Levels"): qui reghdfe log_encounters_nsa log_openings_nsa c.log_openings_nsa#c.(bush2 obama trump biden), a(month year) vce(robust)
qui estadd local qfe "Y"
qui estadd local yfe "Y"
eststo, title("YoY"): qui reg d12_log_encounters_nsa d12_log_openings_nsa c.d12_log_openings_nsa#c.democrat $ts_res_controls, robust
qui estadd local qfe "N"
qui estadd local yfe "N"
eststo, title("YoY"): qui reg d12_log_encounters_nsa d12_log_openings_nsa c.d12_log_openings_nsa#c.(bush2 obama trump biden) $ts_res_controls, robust
qui estadd local qfe "N"
qui estadd local yfe "N"

estout, $estout_params_FE_txt  rename(d12_log_openings_nsa log_openings_nsa ///
									c.d12_log_openings_nsa#c.democrat log_openings_nsa#c.democrat ///
									c.d12_log_openings_nsa#c.bush2 log_openings_nsa#c.bush2 ///
									c.d12_log_openings_nsa#c.obama log_openings_nsa#c.obama ///
									c.d12_log_openings_nsa#c.trump log_openings_nsa#c.trump ///
									c.d12_log_openings_nsa#c.biden log_openings_nsa#c.biden) ///
									keep(log_openings* c.*)
									
estout using "${path}tables/MainResultsPolitics_Monthly.tex", replace $estout_params_FE $estout_pre $estout_post rename(d12_log_openings_nsa log_openings_nsa ///
								c.d12_log_openings_nsa#c.democrat log_openings_nsa#c.democrat ///
								c.d12_log_openings_nsa#c.bush2 log_openings_nsa#c.bush2 ///
								c.d12_log_openings_nsa#c.obama log_openings_nsa#c.obama ///
								c.d12_log_openings_nsa#c.trump log_openings_nsa#c.trump ///
								c.d12_log_openings_nsa#c.biden log_openings_nsa#c.biden) ///
								keep(log_openings* c.*)
eststo clear



//Visualizing estimates
qui reghdfe log_encounters_nsa log_openings_nsa c.log_openings_nsa#c.(clinton bush2 obama trump biden) $ts_res_controls, a(month year) vce(robust)

matrix G = J(1,3,.)
local i = 1
foreach v in bush2 obama trump biden {
	qui lincom _b[log_openings] + _b[c.log_openings#c.`v']
	local beta = r(estimate)
	local se = r(se)
	matrix G = G \ [`i', `beta', `se']
	local i = `i' +1
}

preserve
clear
svmat G
drop if _n==1
ren (G1 G2 G3) (admin beta se)
g ul = beta + 1.645*se
g ll = beta - 1.645*se
label define admin_lbl 1 "Bush" 2 "Obama" 3 "Trump" 4 "Biden"
label values admin admin_lbl
twoway (rcap ul ll admin) (scatter beta admin, msymbol(diamond) msize(normal)) , ytitle("Estimate") xtitle("") xlabel(,angle(45) valuelabel) scheme(s1color) legend(order(2 "Point Estimate" 1 "90% Confidence Interval"))
//twoway (bar beta admin) (rcap ul ll admin), scheme(s1color)
graph export "${path}graphs/EstimationPresidentsLevels_Monthly.eps", as(eps) replace
restore


/*** FIGURES C1 AND C2 ***/
twoway (line openingsrate_nsa date, lp(dash)) (line encounters_nsa date, yaxis(2)) if year>=2018, scheme (s1color) legend(rows(2)) ytitle("Job Openings per Unemployed Ratio") ytitle("Encounters (Raw)", axis(2)) xtitle(Month) legend(order(1 "Job Openings per Unemployed (monthly)" 2 "SW Border Encounters (monthly)"))
graph export "${path}graphs/PlotRawData2018Monthly.eps", as(eps) replace

twoway (line d12_openingsrate_nsa date, lp(dash)) (line d12_encounters_nsa date, yaxis(2)) if year>=2018, scheme (s1color) legend(rows(2)) ytitle("Job Openings per Unemployed Ratio") ytitle("Encounters (Raw)", axis(2)) xtitle(Month) legend(order(1 "Job Openings per Unemployed (monthly, YoY difference)" 2 "SW Border Encounters (monthly, YoY difference)"))
graph export "${path}graphs/PlotYoYDiff2018.eps", as(eps) replace

/*** APPENDIX SECTION E ***/
/* Specific Crossings */
qui ds *_xings
local vlist = r(varlist)

foreach v of local vlist {
	g log_`v'_nsa = log(`v')
}

foreach v in bigbend delrio elcentro elpaso laredo riogrande sandiego tucson yuma {
	eststo,title("`v'"): qui reghdfe log_`v'_xings_nsa log_openings_nsa, a(month year) vce(robust)
	qui estadd local qfe "Y"
	qui estadd local yfe "Y"
}
estout, $estout_params_FE_txt rename(d12_log_openings_nsa log_openings_nsa) keep(log_openings_nsa)
estout using "${path}tables/Results_PerXing_Monthly.tex", replace $estout_params_FE $estout_pre_panelA rename(d12_log_openings_nsa log_openings_nsa) keep(log_openings_nsa)

eststo clear

foreach v in bigbend delrio elcentro elpaso laredo riogrande sandiego tucson yuma {
	g d12_log_`v'_xings_nsa = log_`v'_xings_nsa - L12.log_`v'_xings_nsa
	cap drop res_levels
	qui reg log_`v'_xings_nsa log_openings_nsa
	predict res_levels_`v', res
	global ts_res_controls_`v' l.res_levels_`v' l.(d12_log_encounters_nsa d12_log_openings_nsa)
	
	//global ts_res_controls l.(d4.log_encounters_nsa d4.log_openings_nsa)
	eststo,title("`v'"): qui reg d12_log_`v'_xings_nsa d12_log_openings_nsa $ts_res_controls_`v', robust
	qui estadd local qfe "N"
	qui estadd local yfe "N"
	drop res_levels_`v'
}
estout, $estout_params_FE_txt rename(d12_log_openings_nsa log_openings_nsa) keep(log_openings_nsa)
estout using "${path}tables/Results_PerXing_Monthly.tex", append $estout_params_FE $estout_pre_panelB $estout_post rename(d12_log_openings_nsa log_openings_nsa) keep(log_openings_nsa)
eststo clear

/*** APPENDIX SECTION F ***/
do "${path}do_files/AnalysisQuarterlyData.do" 



/*** FIGURE D1 ***/
do "${path}do_files/AnalysisByNationality.do" 

/*** FIGURE D2 ***/
preserve
use "${path}data/WDIv2022.dta", clear

g temp = ny_gdp_pcap_kd if year==2018
egen gdppckd2018 = mean(temp), by(countrycode)
g gdppckd_relative2018 = 100*ny_gdp_pcap_kd/gdppckd2018		
		twoway 	(line gdppckd_relative2018 year if countrycode =="CHL") ///
				(line gdppckd_relative2018 year if countrycode =="COL") ///
				(line gdppckd_relative2018 year if countrycode =="ECU") ///
				(line gdppckd_relative2018 year if countrycode =="HTI") ///
				(line gdppckd_relative2018 year if countrycode =="HND") ///
				(line gdppckd_relative2018 year if countrycode =="MEX") ///
				(line gdppckd_relative2018 year if countrycode =="NIC") ///
				(line gdppckd_relative2018 year if countrycode =="PER") ///
				(line gdppckd_relative2018 year if countrycode =="SLV") ///
		if year>=2018 & year<=2023, ytitle(GDP Per Capita (Constant USD, 2018=100)) ///
		legend(order(1 "Chile" 2 "Colombia" 3 "Ecuador" 4 "Haiti" 5 "Honduras" 6 "Mexico" 7 "Nicaragua" 8 "Peru" 9 "El Salvador") rows(3)) yline(100, lp(dash) lc(red) lw(thick)) scheme( s2mono ) ytitle("GDP per capita (2018=100)" "Constant prices in national currency") graphregion(color(white))
graph export "${path}graphs/GDP2018.eps", as(eps) replace

drop temp
g temp = sl_uem_totl_zs if year==2018
egen unemp2018 = mean(temp), by(countrycode)
g unemp_relative2018 = 100*  sl_uem_totl_zs/unemp2018

		twoway 	(line unemp_relative2018 year if countrycode =="CHL") ///
				(line unemp_relative2018 year if countrycode =="COL") ///
				(line unemp_relative2018 year if countrycode =="ECU") ///
				(line unemp_relative2018 year if countrycode =="HTI") ///
				(line unemp_relative2018 year if countrycode =="HND") ///
				(line unemp_relative2018 year if countrycode =="MEX") ///
				(line unemp_relative2018 year if countrycode =="NIC") ///
				(line unemp_relative2018 year if countrycode =="PER") ///
				(line unemp_relative2018 year if countrycode =="SLV") ///
		if year>=2018 & year<=2023, ytitle(Unemployment Rate (2018=100)) ///
		legend(order(1 "Chile" 2 "Colombia" 3 "Ecuador" 4 "Haiti" 5 "Honduras" 6 "Mexico" 7 "Nicaragua" 8 "Peru" 9 "El Salvador") rows(3)) yline(100, lp(dash) lc(red) lw(thick)) scheme( s2mono ) ytitle("Unemployment Rate (2018=100)") graphregion(color(white))
graph export "${path}graphs/Unemployment2018.eps", as(eps) replace
restore

